* Table 8 Panel A and Panel B

clear all

* Set your directory path here
local root "D:\Understanding_Bank_Payouts"

capture log close
log using "`root'\Code\Logs\Final_code_RCFS\Table8_PanelA_PanelB.log", replace
display "$S_TIME  $S_DATE"

set more off
set varabbrev off, perm

timer on 1

local finalfile_yearly "`root'\Data\Final\Finalfile_yearly.dta" 


use `finalfile_yearly'
merge m:1 instkey using "`root'\Data\RCFS conditional acceptance\Distance_to_default_2006.dta"
drop _merge
merge 1:1 cusip6 year using "`root'\Data\RCFS conditional acceptance\Institutional_ownership_types_detailed.dta", update
drop if _merge == 2
drop _merge

generate a = pctown_insidersSNL_Y/100
drop pctown_insidersSNL_Y
rename a pctown_insidersSNL_Y

generate avg_logassets_sqY = avg_logassetsY*avg_logassetsY
regress avg_logassets_sqY avg_logassetsY
predict sizesq_orth, residuals
label variable avg_logassetsY "Size (log assets)"
label variable sizesq_orth "Size (log assets) squared, orthogonalized"

rename avg_divdivbookvalueY divbookY
rename divpayoutratioYALT divpayoutY
rename avg_divpayoutratioY divpayoutY_SNL
rename divpershare_year divpershY

winsor tpopshY1, generate(tpopshY1W) p(0.01)

generate avg_tassetsW_BN = avg_totassetsWY/(10^9)

generate avg_tassetsW_BN_sq = avg_tassetsW_BN * avg_tassetsW_BN

xtset instkey year

generate HMDA_to_total3 = (loanvalue + L.loanvalue + L2.loanvalue)/((gross_lns + L.gross_lns + L2.gross_lns)/3)
generate HMDA_to_total2 = (loanvalue + L.loanvalue)/((gross_lns + L.gross_lns)/2)

generate lenientV3 = (loanvalue + L.loanvalue + L2.loanvalue) / (appvalue + L.appvalue + L2.appvalue)
generate lenientC3 = (loancount + L.loancount + L2.loancount) / (appcount + L.appcount + L2.appcount)

generate lenientV2 = (loanvalue + L.loanvalue) / (appvalue + L.appvalue)
generate lenientC2 = (loancount + L.loancount) / (appcount + L.appcount)

foreach var of varlist HMDA_to_total3 lenientV3 lenientC3 {
	generate chg_`var' = D3.`var'
	}

foreach var of varlist HMDA_to_total2 lenientV2 lenientC2 {
	generate chg_`var' = D2.`var'
}


// Make a new variable that captures analyst coverage, including zeroes

generate numest_zeroes = numest
replace numest_zeroes = 0 if numest == .

// Analyst dummy

generate analyst_dummy = 0
replace analyst_dummy = 1 if numest != .

// Generate lag variables to line these up with the 2004-2006 period (since we sort on year 2008 in the quantiles)

generate L2HMDA_to_total3 = L2.HMDA_to_total3 
generate L2lenientV3 = L2.lenientV3

foreach var in fedfunds_balance {
	winsor `var', generate(`var'W) p(0.01)
	label variable `var'W "`var' winsorized at the 1%"
	}

foreach var in fedfunds_balance fedfunds_balanceW {
	by instkey year: egen avg_`var'Y = mean(`var')
	by instkey year: gen `var'4 = `var'[4]  	// The 4th quarter value of the variable in each year
	}

generate avg_fedfunds_repos_scaledWY = (avg_reposWY + avg_fedfunds_balanceWY)/avg_totassetsWY
winsor avg_fedfunds_repos_scaledWY, generate(helper) p(0.01)
drop avg_fedfunds_repos_scaledWY
rename helper avg_fedfunds_repos_scaledWY
label variable avg_fedfunds_repos_scaledWY "Total Fed funds + repos scaled by total assets"


****** Table 8 Panels A and B

label variable avg_tassetsW_BN "Total assets (bn $)"
label variable avg_tassetsW_BN_sq "Total assets squared (bn $)"
label variable avg_mtbWY "Market to book ratio"
label variable avg_roaaWY "ROA"
label variable earningsvol "Earnings volatility"
label variable lev_fps "Leverage"
label variable avg_rwcapitalratioWY "Risk-weighted capital ratio"


foreach y in divpshY1W avg_divyieldY {  

local rhs1 L.avg_logassetsY L.sizesq_orth ///
	L.avg_mtbWY L.avg_roaaWY L.lev_fps L.reteY growth2WY L.avg_tieroneratioWY

local ownership_vars  L.ded_own_perc L.qix_own_perc L.tra_own_perc  ///
	 L.nr_inst_blk_owners  L.pctown_insidersSNL_Y L.x_npr_countY

local rhs2  ///
	L.earningsvol L.avg_int_to_nonintWY L.numest_zeroes ///
			L.avg_stratioY L.HMDA_to_total3 L.lenientV3 L.avg_fedfunds_repos_scaledWY ///
			L.tarpever disttodef2006 

		if "`y'" == "avg_divyieldY" {	// Redefine rhs1 to take the MTB out for dividend yield
			local rhs1 : subinstr local rhs1 "L.avg_mtbWY" ""
			local rhs2 : subinstr local rhs2 "L.avg_mtbWY" ""
			}

		quietly reghdfe `y' `rhs1' if year < 2007, absorb(instkey year) vce(cluster instkey) residuals

		* make sure the previous residuals are not in the file
		capture noisily drop HAT*_ STDP*_ UE*_ U*_ E*_ predicted*_ 
		capture noisily drop E_95to06
		capture noisily drop E_07to08_out
		
		* 1) Get the residuals
		// Save the residuals with and without FE
		quietly xtreg `y' `rhs1' i.year if year < 2007, fe vce(cluster instkey) 

		predict Ef_`y', e
		generate E_95to06 = Ef_`y' 
		
		predict HATf_`y'
		predict STDPf_`y', stdp
		predict UEf_`y', ue
		predict Uf_`y', u
		
		* Need to extend the FE to all years
		by instkey: egen aaa = max(Uf_`y')
		generate bbb = Uf_`y'
		replace Uf_`y' = aaa if bbb == .
		drop aaa bbb

		generate ccc = Ef_`y'
		replace Ef_`y' = UEf_`y' - Uf_`y'
		drop ccc
		generate predictedf_`y' = HATf_`y' + Uf_`y'	// Predicted values (including the FE)
		gen a = HATf_`y' + Uf_`y' + Ef_`y'		// Check that the sums work 
		corr a `y'
		drop a

		generate E_07to08_out = Ef_`y'							// these are the out-of-sample residuals
		replace E_07to08_out = . if year < 2007 | year > 2008		// make sure they are empty out of sample	
		label variable E_07to08_out "Abnormal `y' 2007-2008"

		egen abn_`y'ftile = xtile(E_07to08_out) if year == 2008, nq(4)

		tabstat earningsvol avg_int_to_nonintWY pctown_insidersSNL_Y instown_perc numest_zeroes analyst_dummy ///
			avg_stratioY L2HMDA_to_total3 L2lenientV3 netintexp_ffrepo tarpever avg_rwcapitalratioWY disttodef2006 if year == 2008, by(abn_`y'ftile) 
		
		foreach inst in `ownership_vars' {

		quietly regress E_07to08_out `inst' `rhs2' if (year == 2007 | year == 2008), cluster(instkey)

			outreg2 using Table8_PanelA_PanelB, ///
			excel stats(coef tstat) par(tstat) ctitle(`y') dec(2) label ///
			drop(E_07to08_out) sortvar(`ownership_vars' `rhs2') ///
			addtext(Bank FE 1st step, Yes, Year FE 1st step, Yes, Sample period, 2007-2008) 

			}
		}
	

display "$S_TIME  $S_DATE"
capture log close
clear all

timer off 1
timer list

exit, clear
